/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package assignmentjdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;

/**
 *
 * @author scott
 */
public class DB {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args)
    {
        /* drop the tables */
        String dropBillPayment = "DROP TABLE BillPayment";
        String dropBill = "DROP TABLE Bill";
        String dropBiller = "DROP TABLE Biller";
        String dropArchive = "DROP TABLE Archive";
        String dropBankTransactionType = "DROP TABLE BankTransactionType";
        String dropBankTransaction = "DROP TABLE BankTransaction";
        String dropAccount = "DROP TABLE Account";
        String dropCustomer = "DROP TABLE Customer";

        executeSQLStatement(dropBillPayment);
        executeSQLStatement(dropBill);
        executeSQLStatement(dropBiller);
        executeSQLStatement(dropArchive);
        executeSQLStatement(dropBankTransaction);
        executeSQLStatement(dropBankTransactionType);
        executeSQLStatement(dropAccount);
        executeSQLStatement(dropCustomer);

        /* create the tables */
        String customerSQL = "CREATE TABLE Customer (CustId INT GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_CUSTOMER PRIMARY KEY, Password INT, StaffMember INT)";
        String accountSQL = "CREATE TABLE Account (AccountId BIGINT GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_ACCOUNT PRIMARY KEY, CustId INT, Balance DOUBLE, FOREIGN KEY (CustId) REFERENCES Customer(CustId), Removed INT DEFAULT 0 NOT NULL)";
        String bankTransactionTypeSQL = "CREATE TABLE BankTransactionType (TransType CHAR(1) CONSTRAINT PK_BANKTRANSACTIONTYPE PRIMARY KEY)";
        String bankTransactionSQL = "CREATE TABLE BankTransaction (TransID INT GENERATED ALWAYS AS IDENTITY CONSTRAINT PK_BANKTRANSACTION PRIMARY KEY, TransType CHAR(1), Date BIGINT, FrmAccountId BIGINT, Amount DOUBLE, RecAccountId BIGINT, Description CHAR(128), FOREIGN KEY (TransType) REFERENCES BankTransactionType(TransType))";
        String archiveSQL = "CREATE TABLE Archive (ArchiveId INT GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_ARCHIVE PRIMARY KEY, Date BIGINT, AccountId BIGINT, Balance DOUBLE, FOREIGN KEY (AccountId) REFERENCES Account(AccountId))";
        String billerSQL = "CREATE TABLE Biller (BillerId INT GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_BILLER PRIMARY KEY, Name CHAR(40), AccountId BIGINT, FOREIGN KEY (AccountId) REFERENCES Account(AccountId), UNIQUE (AccountId))";
        String billSQL = "CREATE TABLE Bill (BillId BIGINT GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_BILL PRIMARY KEY, Amount DOUBLE NOT NULL, AmountPaid DOUBLE DEFAULT 0 NOT NULL, CustId INT NOT NULL, BillerAccountId BIGINT NOT NULL, FOREIGN KEY (CustId) REFERENCES Customer(CustId), FOREIGN KEY (BillerAccountId) REFERENCES Biller(AccountId))";
        String billPaymentSQL = "CREATE TABLE BillPayment (Date BIGINT, BillId BIGINT, Amount DOUBLE, CustomerAccountId BIGINT, CONSTRAINT PK_BILLPAYMENT PRIMARY KEY (Date, BillId), FOREIGN KEY (CustomerAccountId) REFERENCES Account(AccountId), FOREIGN KEY (BillId) REFERENCES Bill(BillId))";

        executeSQLStatement(customerSQL);
        executeSQLStatement(accountSQL);
        executeSQLStatement(bankTransactionTypeSQL);
        executeSQLStatement(bankTransactionSQL);
        executeSQLStatement(archiveSQL);
        executeSQLStatement(billerSQL);
        executeSQLStatement(billSQL);
        executeSQLStatement(billPaymentSQL);
        
        /* populate the BankTransactionType table */
        executeSQLStatement("INSERT INTO BankTransactionType VALUES ('B')"); //B = bill payment
        executeSQLStatement("INSERT INTO BankTransactionType VALUES ('D')"); //D = deposit
        executeSQLStatement("INSERT INTO BankTransactionType VALUES ('T')"); //T = money transfer
        executeSQLStatement("INSERT INTO BankTransactionType VALUES ('W')"); //W = withdrawal
        
        /* populate the tables with test data */

        //create customer 1
        executeSQLStatement("INSERT INTO Customer VALUES (1, 10110, 0)");
        executeSQLStatement("INSERT INTO Account VALUES (1, 1, 0, 0)");

        //create customer 12345
        executeSQLStatement("INSERT INTO Customer VALUES (12345, 56789, 0)");
        executeSQLStatement("INSERT INTO Account VALUES (555555, 12345, 25.00, 0)"); //link account 555555 to customer 1
        executeSQLStatement("INSERT INTO Account VALUES (666666, 12345, 54.25, 0)"); //link account 666666 to customer 1
        executeSQLStatement("INSERT INTO Account VALUES (999999, 12345, 1023.70, 0)"); //link account 999999 to customer 1

        //create staff member 98765
        executeSQLStatement("INSERT INTO Customer VALUES (98765, 54321, 1)");

        //create biller 123
        executeSQLStatement("INSERT INTO Customer VALUES (123, 789, 0)");
        executeSQLStatement("INSERT INTO Account VALUES (321, 123, 0, 0)"); //link account 321 to biller 123
        executeSQLStatement("INSERT INTO Biller VALUES (1, 'PayOurBills Inc.', 321)");  //link biller 123 to its customer account

        //create bills
        executeSQLStatement("INSERT INTO Bill VALUES (1, 400.25, 0, 12345, 321)");
        executeSQLStatement("INSERT INTO Bill VALUES (2, 245.70, 0, 12345, 321)");
    }

    public static Connection getConnection() throws SQLException, IOException {
        System.setProperty("jdbc.drivers", "org.apache.derby.jdbc.ClientDriver");
        String url = "jdbc:derby://localhost:1527/Assignment1";
        String username = "APP";
        String password = "APP";
        return DriverManager.getConnection(url, username, password);
    }

    public static void executeSQLStatement(String sql) {
        Connection cnnct = null;
        Statement stmnt = null;
        try {
            cnnct = getConnection();
            stmnt = cnnct.createStatement();
            stmnt.execute(sql);
        } catch (SQLSyntaxErrorException e) {
            e.printStackTrace();
            return;
        } catch (SQLException ex) {
            while (ex != null) {
                ex.printStackTrace();
                ex = ex.getNextException();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            if (stmnt != null) {
                try {
                    stmnt.close();
                } catch (SQLException e) {
                }
            }
            if (cnnct != null) {
                try {
                    cnnct.close();
                } catch (SQLException sqlEx) {
                }
            }
        }
    }
}
